package com.yx.changdao.common.utils;

import com.github.liaochong.myexcel.core.DefaultExcelBuilder;
import com.github.liaochong.myexcel.core.SaxExcelReader;
import com.github.liaochong.myexcel.utils.AttachmentExportUtil;
import com.yx.changdao.common.exception.AlertException;
import org.apache.commons.codec.CharEncoding;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.function.Consumer;

/**
 * Excel 读取导出工具类 需要配合
 *
 * @author xiapeixin
 * @ExcelModel(excludeParent=true,ignoreStaticFields=true)
 * @ExcelColumn(index = 9,title = "账户状态",mapping = "1:开启,0:关闭") 使用
 * @date 2020-4-3
 */

public class ExcelImportExportUtils {

    /**
     * 读取处理Excel文件
     *
     * @param clazz         用什么实体类接收
     * @param multipartFile 上传的文件
     * @param consumer      具体的业务逻辑
     * @param <T>           实体类泛型
     * @throws AlertException
     */
    public static <T> void readExcel(Class<T> clazz, MultipartFile multipartFile,
                                     Consumer<T> consumer) throws AlertException {
        try {
            // （推荐）方式二：读取一行处理一行，可自行决定终止条件，SAX模式，避免OOM，建议大量数据使用
            // readThen有两种重写接口，返回Boolean型接口允许在返回False情况下直接终止读取
            SaxExcelReader.of(clazz)
                    .sheet(0) // 获取第一个sheet
                    .rowFilter(row -> row.getRowNum() > 1) // 过滤第0,1行,是标题
                    // .beanFilter(t -> t.getIfTeam().equals(1)) // 根据bean的条件过滤
                    .readThen(multipartFile.getInputStream(), consumer);// 可接收inputStream
        } catch (Exception e) {
            throw new AlertException("读取excel失败");
        }
    }

    /**
     * 输出excel
     *
     * @param clazz    用什么实体类模板写出
     * @param list     数据
     * @param response 响应对象
     * @param <T>      实体类泛型
     * @throws IOException
     * @throws AlertException
     */
    public static <T> void writeExcel(Class<T> clazz, List<T> list,
                                      HttpServletResponse response, String fileName) throws IOException, AlertException {
        if (list.isEmpty()) {
            response.getOutputStream().close();
        }
        try {
            Workbook workbook = DefaultExcelBuilder.of(clazz).build(list);

            AttachmentExportUtil.export(workbook, fileName, response);
        } catch (Exception e) {
            throw new AlertException("导出excel错误");
        }

    }

    /**
     * 导出excel 根据字段导出
     * @param list 数据载体
     * @param response  响应对象
     * @param fileName  导出文件名
     * @param cloums  需要导出的字段 "类属性名:列名称" 例如 "residentName:反馈居民姓名"
     *                          如果只想要列名 没有具体值,那么直接写列名 例如 "满意","基本满意","不满意"
     *
     *                      测试案例 ExcelImportExportUtils.writeExcelByCloum(list,response,"导出",
     *                            "residentName:反馈居民姓名",
     *                           "residentPhone:反馈居民手机号",
     *                            "category:意见建议类别",
     *                            "content:意见建议内容",
     *                            "createTime:创建时间",
     *                          "满意","基本满意","不满意");
     */
    public static <T> void writeExcelByCloum(List<T> list, HttpServletResponse response,
                                             String fileName, List<String> cloums) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet(fileName);

        XSSFRow row = sheet.createRow(0);

        List<String> cloun = new ArrayList<>();

        for (int i = 0; i < cloums.size(); i++) {

            String[] split = cloums.get(i).split(":");

            if(split.length==2){
                cloun.add(split[0]);
                row.createCell(i).setCellValue(split[1]);
            }else{
                row.createCell(i).setCellValue(cloums.get(i));
            }
        }

        for (int i = 0; i < list.size(); i++) {
            XSSFRow temp = sheet.createRow(i + 1);

            T t = list.get(i);

            Class<?> aClass = t.getClass();

            for (int j = 0; j < cloun.size(); j++) {

                Field field = null;
                try{
                    field = aClass.getDeclaredField(cloun.get(j));
                }catch (Exception e){
                    Class<?> superclass = aClass.getSuperclass();
                    field = superclass.getDeclaredField(cloun.get(j));
                }

                field.setAccessible(true);
                Object o = field.get(t);

                Object resul = null;

                if(o instanceof Date){
                    resul =  Dates.formatDetail().format((Date) o);
                }else{
                    resul = o;
                }

                temp.createCell(j).setCellValue(resul == null ? "" : resul.toString());
            }
        }


        response.setCharacterEncoding(CharEncoding.UTF_8);
        response.addHeader("Content-Disposition", "attachment;filename=" +
                URLEncoder.encode(fileName.concat(".xlsx"), CharEncoding.UTF_8).replace("+", "%20"));

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //文档输出
        workbook.write(response.getOutputStream());
        response.getOutputStream().close();
    }

}